package org.linlinjava.litemall.db.dao;

import java.util.List;

import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.linlinjava.litemall.db.domain.MerchandiseStatisticsVo;
import org.linlinjava.litemall.db.vo.shopmerchandise.ConsumerStatisticsVO;
import org.linlinjava.litemall.db.vo.shopmerchandise.InNumStatisticsVO;
import org.linlinjava.litemall.db.vo.shopmerchandise.LossStatisticsVO;

public interface LitemallMerchandiseMapperEx {
   
	IPage<MerchandiseStatisticsVo> statistics(IPage iPage,
											 @Param("merchandiseName") String merchandiseName,
											 @Param("merchandiseSn") String merchandiseSn,
											 @Param("beginTime") String beginTime,
											 @Param("endTime") String endTime);



	@Select("SELECT adm.merchandise_id AS merchandiseId, ao.shop_id AS shopId, SUM(adm.number) AS inNum " +
			"FROM litemall_admin_order_merchandise adm " +
			"JOIN litemall_admin_order ao ON ao.id = adm.admin_order_id " +
			"WHERE ao.order_status = 8 AND TO_DAYS(ao.end_time)  BETWEEN TO_DAYS(#{beginTime}) AND TO_DAYS(#{endTime}) " +
			"GROUP BY adm.merchandise_id, ao.shop_id")
	List<InNumStatisticsVO> getInNumStatistics(@Param("beginTime") String beginTime, @Param("endTime") String endTime);

	@Select("SELECT om.shop_id AS shopId, om.merchandise_id AS merchandiseId, SUM(om.consumer_number) AS consumerNum " +
			"FROM litemall_order_merchandise om " +
			"WHERE TO_DAYS(om.add_time) BETWEEN TO_DAYS(#{beginTime}) AND TO_DAYS(#{endTime})  " +
			"GROUP BY om.merchandise_id, om.shop_id")
	List<ConsumerStatisticsVO> getConsumerStatistics(@Param("beginTime") String beginTime, @Param("endTime") String endTime);
	@Select("SELECT ms.shop_id AS shopId, ms.merchandise_id AS merchandiseId, SUM(sci.loss_number) AS lossNum " +
			"FROM litemall_shop_merchandise ms " +
			"JOIN litemall_shop_check_item sci ON ms.id = sci.shop_merchandise_id " +
			"WHERE TO_DAYS(sci.add_time) BETWEEN TO_DAYS(#{beginTime}) AND TO_DAYS(#{endTime})  " +
			"GROUP BY ms.shop_id, ms.merchandise_id")
	List<LossStatisticsVO> getLossStatistics(@Param("beginTime") String beginTime, @Param("endTime") String endTime);
}